/**
 * 
 */
package com.clh.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.clh.dao.AnnouncementDao;
import com.clh.entity.Announcement;
import com.clh.util.DBUtil;

/**
 * @Description: 公告Dao实现类
 * @author 邱凯臻
 * @date 2022年5月25日 下午3:19:52
 * @version v1.0
 */
public class AnnouncementDaoImpl implements AnnouncementDao {

	/**
	 * 查询所有公告
	 * 
	 * @return
	 */
	@Override
	public List<Announcement> findAll() {
		List<Announcement> list = new ArrayList<Announcement>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" *").append(" from announcement");
		// System.out.println(sql);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				String annoNo = rs.getString("anno_no");
				String annoContent = rs.getString("anno_content");
				Date annoTime = rs.getDate("anno_time");
				String annoTitle = rs.getString("anno_title");

				Announcement announcement = new Announcement();
				announcement.setAnnoNo(annoNo);
				announcement.setAnnoContent(annoContent);
				announcement.setAnnoTime(annoTime);
				announcement.setAnnoTitle(annoTitle);

				list.add(announcement);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Announcement> findByCondition(Announcement announcement) {
		List<Announcement> list = new ArrayList<Announcement>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" *").append(" from announcement");
		if (true) {
			sql.append(" where 1 = 1");
		}

		if (announcement.getAnnoNo() != null && !"".equals(announcement.getAnnoNo())) {
			sql.append(" and anno_no = '" + announcement.getAnnoNo() + "'");
		}

		if (announcement.getAnnoTitle() != null && !"".equals(announcement.getAnnoTitle())) {
			sql.append(" and anno_title like '%" + announcement.getAnnoTitle() + "%'");
		}

		if (announcement.getAnnoTime() != null && !"".equals(announcement.getAnnoTime())) {
			sql.append(" and anno_time = '" + announcement.getAnnoTime() + "'");
		}

		 //System.out.println(sql);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				String annoNo = rs.getString("anno_no");
				String annoContent = rs.getString("anno_content");
				Date annoTime = rs.getDate("anno_time");
				String annoTitle = rs.getString("anno_title");

				Announcement announcement_temp = new Announcement();
				announcement_temp.setAnnoNo(annoNo);
				announcement_temp.setAnnoContent(annoContent);
				announcement_temp.setAnnoTime(annoTime);
				announcement_temp.setAnnoTitle(annoTitle);

				list.add(announcement_temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Announcement> findByPage(Announcement announcement, int pageNum, int pageSize, String startDate,
			String endDate) {

		// System.out.println("Dao pageNum = " + pageNum);
		// System.out.println("Dao pageSize = " + pageSize);

		List<Announcement> list = new ArrayList<Announcement>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" *").append(" from announcement");
		if (true) {
			sql.append(" where 1 = 1");
		}

		if (announcement.getAnnoTitle() != null && !"".equals(announcement.getAnnoTitle())) {
			sql.append(" and anno_title like '%" + announcement.getAnnoTitle() + "%'");
		}
		if (startDate != null && !"".equals(startDate)) {
			sql.append(" and anno_time >= '" + startDate + "'");
		}
		if (endDate != null && !"".equals(endDate)) {
			sql.append(" and anno_time <= '" + endDate + "'");
		}
		if ((!"".equals(pageNum) || pageNum >= 0) || (!"".equals(pageSize) || pageSize >= 0)) {
			sql.append(" limit " + (pageNum - 1) * pageSize + "," + pageSize);
		}

		// System.out.println(sql);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				String annoNo = rs.getString("anno_no");
				String annoContent = rs.getString("anno_content");
				Date annoTime = rs.getDate("anno_time");
				String annoTitle = rs.getString("anno_title");

				Announcement announcement_temp = new Announcement();
				announcement_temp.setAnnoNo(annoNo);
				announcement_temp.setAnnoContent(annoContent);
				announcement_temp.setAnnoTime(annoTime);
				announcement_temp.setAnnoTitle(annoTitle);

				list.add(announcement_temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	@Override
	public int countByCondition(Announcement announcement, String startDate, String endDate) {
		int n = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" count(*)").append(" from announcement");
		if (true) {
			sql.append(" where 1 = 1");
		}

		if (announcement.getAnnoTitle() != null && !"".equals(announcement.getAnnoTitle())) {
			sql.append(" and anno_title like '%" + announcement.getAnnoTitle() + "%'");
		}
		if (startDate != null && !"".equals(startDate)) {
			sql.append(" and anno_time >= '" + startDate + "'");
		}
		if (endDate != null && !"".equals(endDate)) {
			sql.append(" and anno_time <= '" + endDate + "'");
		}

		// System.out.println(sql);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// System.out.println("count = " + n);

		return n;
	}

	@Override
	public int add(Announcement announcement) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into announcement (anno_no,anno_content,anno_time,anno_title)").append(" values (?,?,?,?)");
		Date currentTime = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
		String annoNo = formatter.format(currentTime);
		return DBUtil.update(sql.toString(), annoNo, announcement.getAnnoContent(), announcement.getAnnoTime(),
				announcement.getAnnoTitle());
	}

	@Override
	public int update(Announcement announcement) {
		String sql = "update announcement set anno_content=?,anno_time=?,anno_title=? where anno_no=?";
		return DBUtil.update(sql, announcement.getAnnoContent(), announcement.getAnnoTime(),
				announcement.getAnnoTitle(), announcement.getAnnoNo());
	}
}